﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model.EntityModel;

namespace DataAccess
{
    public static class RequestingMedicalDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=Brigada; Integrated Security=true";

        public static RequestingMedical InsertRequest(RequestingMedical newPerson)
        {
            BrigadaEntities context = new BrigadaEntities();
            context.AddToRequestingMedical(newPerson);
            context.SaveChanges();
            return newPerson;
        }      

        public static RequestingMedical GetRequestByIdComplaint(long idComplaint)
        {
            BrigadaEntities context = new BrigadaEntities();
            var request = (from p in context.RequestingMedical
                          where p.IdComplaint == idComplaint
                          select p).SingleOrDefault();
            return request;
        }

        public static RequestingMedical UpdateRequest(long ciPerson, RequestingMedical newInfo)
        {
            BrigadaEntities context = new BrigadaEntities();
            var person = (from p in context.RequestingMedical
                          where p.Id == ciPerson
                          select p).Single();
            person.IdComplaint = newInfo.IdComplaint;
            person.MedicalCenter = newInfo.MedicalCenter;
            person.MedicalName = newInfo.MedicalName;
            person.MedicalPaternalName = newInfo.MedicalPaternalName;
            person.MedicalMaternalName = newInfo.MedicalMaternalName;
            context.SaveChanges();
            return person;
        }

        public static RequestingMedical InsertRequestingMedical(RequestingMedical newRequest)
        {
            if (newRequest != null)
            {
                SqlConnection connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    String text = "INSERT INTO [dbo].[RequestingMedical](Id,IdComplaint,MedicalName,MedicalPaternalName, MedicalMaternalName, MedicalCenter, Status,RegistrationDate)";
                    text = text + "VALUES(@Id,@IdComplaint,@MedicalName,@MedicalPaternalName, @MedicalMaternalName, @MedicalCenter, @Status,@RegistrationDate)";
                    command.CommandText = text;

                    command.Parameters.AddWithValue("Id", newRequest.Id);
                    command.Parameters.AddWithValue("IdComplaint", newRequest.IdComplaint);
                    command.Parameters.AddWithValue("MedicalName", newRequest.MedicalName);
                    command.Parameters.AddWithValue("MedicalPaternalName", newRequest.MedicalPaternalName);
                    command.Parameters.AddWithValue("MedicalMaternalName", newRequest.MedicalMaternalName);
                    command.Parameters.AddWithValue("MedicalCenter", newRequest.MedicalCenter);
                    command.Parameters.AddWithValue("Status", newRequest.Status);
                    command.Parameters.AddWithValue("RegistrationDate", newRequest.RegistrationDate);

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        return null;
                    }
                    return newRequest;
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static RequestingMedical UpdateRequestingMedical(RequestingMedical newRequest)
        {
            if (newRequest != null)
            {
                SqlConnection connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    String text = "UPDATE [dbo].[RequestingMedical] SET Id=@Id,IdComplainant=@IdComplaint,MedicalName=@MedicalName,MedicalPaternalName=@MedicalPaternalName, MedicalMaternalName=@MedicalMaternalName,MedicalCenter= @MedicalCenter,Status= @Status,RegistrationDate=@RegistrationDate)";
                    command.CommandText = text;

                    command.Parameters.AddWithValue("Id", newRequest.Id);
                    command.Parameters.AddWithValue("IdComplaint", newRequest.IdComplaint);
                    command.Parameters.AddWithValue("MedicalName", newRequest.MedicalName);
                    command.Parameters.AddWithValue("MedicalPaternalName", newRequest.MedicalPaternalName);
                    command.Parameters.AddWithValue("MedicalMaternalName", newRequest.MedicalMaternalName);
                    command.Parameters.AddWithValue("MedicalCenter", newRequest.MedicalCenter);
                    command.Parameters.AddWithValue("Status", newRequest.Status);
                    command.Parameters.AddWithValue("RegistrationDate", newRequest.RegistrationDate);

                    connection.Open();
                    command.ExecuteNonQuery();
                    
                    return newRequest;
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static RequestingMedical GetRequestMedicalByComplaint(long IdComplaint)
        {
            RequestingMedical person = new RequestingMedical();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [RequestingMedical] WHERE IdComplaint=@IdComplaint";
                cmd.Parameters.AddWithValue("IdComplaint", IdComplaint);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    person.Id = Convert.ToInt64(reader["Id"].ToString());
                    person.IdComplaint = IdComplaint;
                    person.MedicalCenter = reader["MedicalCenter"].ToString();
                    person.MedicalMaternalName = reader["MedicalMaternalName"].ToString();
                    person.MedicalName = reader["MedicalName"].ToString();
                    person.MedicalPaternalName = reader["MedicalPaternalName"].ToString();                    
                }
                return person;
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
